<?php

class SQLiteDB{
	const TABLENAME = "Producer";
	const PRODUCER_FILENAME = "Producer.sqlite";
	const CONTENT_FILENAME = "data.txt";
	private $database = NULL;
	
	public function CreateConnection(){
		try{
			$this->database = new PDO('sqlite:'.SQLiteDB::PRODUCER_FILENAME);
			$this->database->SetAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		
		}catch(PDOException $e){
			die('ERROR OCCURED! ->' .$e->getMessage());
		}
		return $this->database;
	}
	
	public function CloseConnection(){
		$this->database = NULL;
	}
	
	public function CreateSQLiteDB(){
		
		$database = $this->CreateConnection();
		
		$query = 'CREATE TABLE IF NOT EXISTS '.SQLiteDB::PRODUCER_TABLENAME.'
				 (producerID INTEGER PRIMARY KEY AUTOINCREMENT, 
				 longitude REAL,
				 latitude REAL,
				 description TEXT);';
	 	
		try{
			$sth = $database->prepare($query);
			if(!$sth->execute()){
				die("Error when creating table");
			}
		}
		catch(PDOException $e){
			die('ERROR OCCURED! ->' .$e->getMessage());
		}
		
		$this->FillProducerTable($database);
		
		$this->CloseConnection();
	}
	
	public function FillProducerTable($database){
		$arr = array();
		$filename = SQLiteDB::CONTENT_FILENAME;
		if(!file_exists($filename)){
			return FALSE;
		}
		
		try{
			$rows = file($filename);
		}catch(exception $e){
			die("Error : $e");
		}
		
		foreach ($rows as $row) {
			
			$arr = explode(',', $row);
			$query = "INSERT OR IGNORE INTO ".SQLiteDB::PRODUCER_TABLENAME." (producerID, longitude, latitude, description) "
					 ."VALUES (".trim($arr[2]).", ".trim($arr[0]).", ".trim($arr[1]).", '".trim($arr[3])."');";
			try{
				$sth = $database->prepare($query);
			}catch(PDOException $e){
				die('ERROR OCCURED! ->' .$e->getMessage());
			}
			if(!$sth->execute()){
				die("Error when creating table");
			}
		}
	}
}
